package cn.com.dashihui.web.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.common.OrderCode;
import cn.com.dashihui.web.common.SerOrderCode;
import cn.com.dashihui.web.common.ServiceOrderCode;

public class ReportService {
	
	/*店铺商品订单统计*/
	
	/**
	 * 查询指定店铺的订单统计数据
	 * @param storeid
	 */
	public Map<String,Object> queryOrderStatesCount(int storeid){
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		//1.商品总量
		sqlBuffer.append("(SELECT COUNT(*) FROM v_goods WHERE storeid=?) goodsCount,");
		sqlParams.add(storeid);
		
		//2.统计“待付款”的商品订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE storeid=? AND payType=? AND payState=? AND orderState=?) state1,");
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);
		sqlParams.add(OrderCode.OrderPayState.NO_PAY);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//3.统计查询“待发货”的订单数量（1：“在线支付+送货上门”、“已支付”、“未发货”、“正常”，2：“货到付款+送货上门”、“未发货”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE storeid=? AND takeType=?");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(payType=? AND payState=?)");
		sqlBuffer.append(" OR payType=?");
		sqlBuffer.append(") AND deliverState=? AND orderState=?) state2,");
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);
		sqlParams.add(OrderCode.OrderDeliverState.NO_DELIVER);sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//4.统计查询“待签收”的订单数量（“送货上门”、“已发货”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE storeid=?");
		sqlBuffer.append(" AND takeType=? AND deliverState=?");
		sqlBuffer.append(" AND orderState=?) state3,");
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);sqlParams.add(OrderCode.OrderDeliverState.HAD_DELIVER);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//5.统计查询“待取货”的订单数量（1：“在线支付+门店自取”、“已支付”、“正常”，2：“货到付款+门店自取”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE storeid=?");
		sqlBuffer.append(" AND (");
		sqlBuffer.append(" (payType=? AND takeType=? AND payState=?)");
		sqlBuffer.append(" OR (payType=? AND takeType=?)");
		sqlBuffer.append(") AND orderState=?) state4,");
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderTakeType.TAKE_SELF);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);sqlParams.add(OrderCode.OrderTakeType.TAKE_SELF);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//6.统计查询“被催单”的订单数量（“正常”、“催单次数!=0”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE storeid=?");
		sqlBuffer.append(" AND orderState=? AND urgeTimes!=0) state5");
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderState.NORMAL);

		Record result = Db.findFirst(sqlBuffer.toString(),sqlParams.toArray());
		return result.getColumns();
	}
	
	/**
	 * 查询指定店铺的订单统计图表数据
	 * @param storeid
	 */
	public List<Record> queryOrderOfSevenDay(int storeid){
		String sql = "SELECT"
				+ " DATE_FORMAT(DD.dateDate,'%m月%d日') date,IFNULL(B.total,0) total"
				+ " FROM t_dict_date DD"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_order A WHERE A.storeid=? AND A.createDate BETWEEN ? AND ? GROUP BY DOFY) B ON DD.dateDate=B.DOFY"
				+ " WHERE DD.dateDate BETWEEN ? AND ?"
				+ " ORDER BY DD.dateDate ASC";
		String timeFrom = getDateTimeBefore(9), timeTo = getNowDateTime();
		return Db.find(sql,storeid,timeFrom,timeTo,timeFrom,timeTo);
	}
	
	/**
	 * 查询指定店铺的交易成功订单统计图表数据
	 * @param storeid
	 */
	public List<Record> queryFinishOrderOfSevenDay(int storeid){
		String sql = "SELECT"
				+ " DATE_FORMAT(DD.dateDate,'%m月%d日') date,IFNULL(B.total,0) total"
				+ " FROM t_dict_date DD"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_order A WHERE A.storeid=? AND A.createDate BETWEEN ? AND ? AND orderState=? GROUP BY DOFY) B ON DD.dateDate=B.DOFY"
				+ " WHERE DD.dateDate BETWEEN ? AND ?"
				+ " ORDER BY DD.dateDate ASC";
		return Db.find(sql,storeid,getDateTimeBefore(10),getNowDateTime(),OrderCode.OrderState.FINISH,getDateTimeBefore(10),getNowDateTime());
	}
	
	/**
	 * 查询订单统计情况
	 */
	public List<Record> queryOrderCountPage(int storeid,String beginDate,String endDate){
		StringBuffer sBuffer = new StringBuffer("SELECT * FROM t_report_order_deal A WHERE A.storeid=?");
		List<Object> params = new ArrayList<Object>();
		params.add(storeid);
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY A.date DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	
	/**
	 * 分页查询商品成交统计情况
	 */
	public Page<Record> queryGoodsRankPage(int pageNum,int pageSize,int storeid,String beginDate,String endDate){
		StringBuffer sBuffer = new StringBuffer("FROM (SELECT r.* FROM (SELECT rgs.name,rgs.categoryName category,SUM(rgs.count) goodsCount,SUM(rgs.price) goodsAmount FROM (SELECT A.name,A.price,A.count,C.categoryName"
				+ " FROM t_bus_order_list A"
				+ " INNER JOIN t_bus_order O ON A.orderNum=O.orderNum"
				+ " INNER JOIN t_bus_goods G ON A.goodsid=G.id"
				+ " INNER JOIN t_dict_category C ON G.categoryonid=C.categoryId"
				+ " WHERE O.orderState=? AND O.storeid=?");
		List<Object> params = new ArrayList<Object>();
		params.add(OrderCode.OrderState.FINISH);params.add(storeid);
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(") rgs GROUP BY rgs.name,rgs.categoryName) r ORDER BY r.goodsCount DESC) rr");
		//查询分页数据
		Page<Record> page = Db.paginate(pageNum, pageSize, "SELECT rr.*", sBuffer.toString(), params.toArray());
		//为每一条记录设置序号，即排行
		if(page!=null){
			List<Record> list = page.getList();
			for(int i=0;i<list.size();i++){
				list.get(i).set("index", (i+1)+(pageNum-1)*pageSize);
			}
		}
		return page;
	}
	
	/*商家服务订单统计*/
	
	/**
	 * 查询指定店铺的商家服务订单统计数据
	 * @param storeid
	 */
	public Map<String,Object> querySerOrderStatesCount(int storeid){
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		//1.统计查询“待付款”的服务订单数量+家政服务订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE storeid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE storeid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state1,");
		
		//2.1.统计查询“待派单”的服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE storeid=? AND ((payType=? AND payState=?) OR payType=?) AND deliverState=? AND orderState=?) state2_1,");
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(ServiceOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(ServiceOrderCode.OrderDispatchState.NO_DISPATCH);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		
		//2.2.统计查询“待接单”的家政服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE storeid=? AND ((payType=? AND payState=?) OR payType=?) AND deliverState=? AND orderState=?) state2_2,");
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(SerOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(SerOrderCode.OrderDispatchState.STORE_NO_ACCEPT);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		
		//2.3.统计查询“待派单”的家政服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE storeid=? AND ((payType=? AND payState=?) OR payType=?) AND deliverState=? AND (orderState=? OR orderState=?)) state2_3,");
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(SerOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(SerOrderCode.OrderDispatchState.STORE_HAD_ACCEPT);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlParams.add(SerOrderCode.OrderState.REFUSE);
		
		//3.统计查询“待确认”的服务订单数量+家政服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE storeid=? AND ((payType=? AND payState=?) OR payType=?) AND deliverState=? AND orderState=?)");
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(ServiceOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(ServiceOrderCode.OrderDispatchState.HAD_DISPATCH);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE storeid=? AND ((payType=? AND payState=?) OR payType=?) AND deliverState=? AND orderState=?)");
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(SerOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(SerOrderCode.OrderDispatchState.PROSER_HAD_ACCEPT);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state3,");
		
		//4.统计查询“被催单”的订单数量（“正常”、“催单次数!=0”）
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE storeid=? AND orderState=? AND urgeTimes!=0)");
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE storeid=? AND orderState=? AND urgeTimes!=0)");
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state4");

		Record result = Db.findFirst(sqlBuffer.toString(),sqlParams.toArray());
		return result.getColumns();
	}
	
	/**
	 * 查询指定店铺的商家服务订单统计图表数据
	 * @param storeid
	 */
	public List<Record> queryServiceOrderOfSevenDay(int storeid){
		String sql = "SELECT"
				+ " DATE_FORMAT(DD.dateDate,'%m月%d日') date,IFNULL(B.total+C.total,0) total"
				+ " FROM t_dict_date DD"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_service_order A WHERE A.storeid=? AND A.createDate BETWEEN ? AND ? GROUP BY DOFY) B ON DD.dateDate=B.DOFY"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_ser_order A WHERE A.storeid=? AND A.createDate BETWEEN ? AND ? GROUP BY DOFY) C ON DD.dateDate=C.DOFY"
				+ " WHERE DD.dateDate BETWEEN ? AND ?"
				+ " ORDER BY DD.dateDate ASC";
		String timeFrom = getDateTimeBefore(9), timeTo = getNowDateTime();
		return Db.find(sql,storeid,timeFrom,timeTo,storeid,timeFrom,timeTo,timeFrom,timeTo);
	}
	
	/**
	 * 查询指定店铺的交易成功订单统计图表数据
	 * @param storeid
	 */
	public List<Record> queryFinishServiceOrderOfSevenDay(int storeid){
		String sql = "SELECT"
				+ " DATE_FORMAT(DD.dateDate,'%m月%d日') date,IFNULL(B.total+C.total,0) total"
				+ " FROM t_dict_date DD"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_service_order A WHERE A.storeid=? AND A.createDate BETWEEN ? AND ? AND orderState=? GROUP BY DOFY) B ON DD.dateDate=B.DOFY"
				+ " LEFT JOIN (SELECT DATE_FORMAT(A.createDate,'%Y-%m-%d') DOFY,COUNT(*) total FROM t_bus_ser_order A WHERE A.storeid=? AND A.createDate BETWEEN ? AND ? AND orderState=? GROUP BY DOFY) C ON DD.dateDate=C.DOFY"
				+ " WHERE DD.dateDate BETWEEN ? AND ?"
				+ " ORDER BY DD.dateDate ASC";
		String timeFrom = getDateTimeBefore(9), timeTo = getNowDateTime();
		return Db.find(sql,storeid,timeFrom,timeTo,ServiceOrderCode.OrderState.FINISH,storeid,timeFrom,timeTo,SerOrderCode.OrderState.FINISH,timeFrom,timeTo);
	}
	
	/**
	 * 查询服务订单统计情况
	 */
	public List<Record> queryServiceOrderCountPage(int storeid,String beginDate,String endDate){
		StringBuffer sBuffer = new StringBuffer("SELECT * FROM t_report_service_order_deal A WHERE A.storeid=?");
		List<Object> params = new ArrayList<Object>();
		params.add(storeid);
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY A.date DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	
	/**
	 * 分页查询服务商家成交统计情况
	 */
	public Page<Record> queryServiceShopRankPage(int pageNum,int pageSize,int storeid,String beginDate,String endDate){
		StringBuffer sBuffer = new StringBuffer(" FROM (SELECT r.* FROM (SELECT rgs.shopThumb,rgs.shopName,COUNT(*) shopCount,SUM(rgs.amount) shopAmount FROM (SELECT A.shopThumb,A.shopName,A.amount"
				+ " FROM t_bus_service_order_list A"
				+ " INNER JOIN t_bus_service_order O ON A.orderNum=O.orderNum"
				+ " WHERE O.orderState=? AND O.storeid=?");
		List<Object> params = new ArrayList<Object>();
		params.add(OrderCode.OrderState.FINISH);params.add(storeid);
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(") rgs GROUP BY rgs.shopThumb,rgs.shopName) r ORDER BY r.shopCount DESC) rr");
		//查询分页数据
		Page<Record> page = Db.paginate(pageNum, pageSize, "SELECT rr.*", sBuffer.toString(), params.toArray());
		//为每一条记录设置序号，即排行
		if(page!=null){
			List<Record> list = page.getList();
			for(int i=0;i<list.size();i++){
				list.get(i).set("index", (i+1)+(pageNum-1)*pageSize);
			}
		}
		return page;
	}
	
	/**
	 * 分页查询服务项成交统计情况
	 */
	public Page<Record> queryServiceItemRankPage(int pageNum,int pageSize,int storeid,String beginDate,String endDate){
		StringBuffer sBuffer = new StringBuffer(" FROM (SELECT r.* FROM (SELECT rgs.shopThumb,rgs.shopName,rgs.serviceTitle,COUNT(*) serviceCount,SUM(rgs.amount) serviceAmount FROM (SELECT A.shopThumb,A.shopName,A.serviceTitle,A.amount"
				+ " FROM t_bus_service_order_list A"
				+ " INNER JOIN t_bus_service_order O ON A.orderNum=O.orderNum"
				+ " WHERE O.orderState=? AND O.storeid=?");
		List<Object> params = new ArrayList<Object>();
		params.add(OrderCode.OrderState.FINISH);params.add(storeid);
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(O.signDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(") rgs GROUP BY rgs.shopThumb,rgs.shopName,rgs.serviceTitle) r ORDER BY r.serviceCount DESC) rr");
		//查询分页数据
		Page<Record> page = Db.paginate(pageNum, pageSize, "SELECT rr.*", sBuffer.toString(), params.toArray());
		//为每一条记录设置序号，即排行
		if(page!=null){
			List<Record> list = page.getList();
			for(int i=0;i<list.size();i++){
				list.get(i).set("index", (i+1)+(pageNum-1)*pageSize);
			}
		}
		return page;
	}
	
	/*工具方法*/
	private SimpleDateFormat ymd = new SimpleDateFormat("yyyy-MM-dd");
	private SimpleDateFormat ymdhms = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	//获取当前日期时间
	private String getNowDateTime(){
		return ymdhms.format(new Date());
	}
	//获取N天前的日期时间
	private String getDateTimeBefore(int amount){
		Calendar calc = Calendar.getInstance(Locale.CHINESE);
		calc.add(Calendar.DATE, -amount);
		return ymd.format(calc.getTime())+" 00:00:00";
	}
}
